﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using PPIDataAccess.General;
using System.Data;

namespace PPIDataAccess.Data
{
    public class DUtility_Asset_XMissionStructure_Image
    {
        #region Query
        
        /// <summary>
        /// Get All Images by offset
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAll(Int32 offset, string connectionKey)
        {
            SqlDataAdapter _Da = null;
            try
            {
                _Da = new SqlDataAdapter("up_Utility_Asset_XMissionStructures_Images_SelectAll", DBHelper.GetConnection(connectionKey));
                _Da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet _Ds = new DataSet();
                _Da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@offset", ParameterDirection.Input, false, offset));
                _Da.Fill(_Ds);
                return _Ds;
            }
            catch (Exception _Ex)
            {
                throw new Exception("Error executing up_Utility_Asset_XMissionStructures_Images_SelectAll", _Ex);
            }
            finally
            {
                if (_Da != null) _Da.SelectCommand.Connection.Close();
            }

        }

        /// <summary>
        /// get all images associated to the utility
        /// </summary>
        /// <param name="utility"></param>
        /// <returns></returns>
        public static DataSet SelectAllByUtility(Int32 utility, string connectionKey)
        {
            SqlDataAdapter _Da = null;
            try
            {
                _Da = new SqlDataAdapter("up_Utility_Asset_XMissionStructures_Images_SelectAllByUtility", DBHelper.GetConnection(connectionKey));
                _Da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet _Ds = new DataSet();
                _Da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@utilityID", ParameterDirection.Input, false, utility));
                _Da.Fill(_Ds);
                return _Ds;
            }
            catch (Exception _Ex)
            {
                throw new Exception("Error executing up_Utility_Asset_XMissionStructures_Images_SelectAllByUtility", _Ex);
            }
            finally
            {
                if (_Da != null) _Da.SelectCommand.Connection.Close();
            }
        }


        #endregion


        #region CRUD

        public static void Disassociate(int imageID, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Images_Disassociate", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@imageID", ParameterDirection.Input, false, imageID));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Update Utility_Asset_XMissionStructures_PoleFK to null
        /// </summary>
        /// <param name="poleId"></param>
        public static void DisassociatePole(int poleId, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Images_DisassociatePole", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@poleId", ParameterDirection.Input, false, poleId));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Update Utility_Asset_XMissionStructures_PoleFK to poleId by Utility_Asset_XMissionStructures_ImageId
        /// </summary>
        /// <param name="poleId"></param>
        public static void Associate(int imageId, int poleId, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Images_AssociatePole", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@imageid", ParameterDirection.Input, false, imageId));
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@poleId", ParameterDirection.Input, false, poleId));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Insert new Image
        /// </summary>
        /// <param name="vendor_Visit_XMissionStructureFK"></param>
        /// <param name="solutionAvaible"></param>
        /// <param name="fileName"></param>
        /// <param name="bytes"></param>
        /// <param name="latitude"></param>
        /// <param name="altitude"></param>
        /// <param name="longitude"></param>
        /// <param name="satelliteTime"></param>
        /// <param name="satelliteCount"></param>
        /// <param name="direction"></param>
        /// <param name="vendor_Visit_XMissionStructures_PoleID"></param>
        /// <returns></returns>
        public static int Insert(int vendor_Visit_XMissionStructureFK, bool solutionAvaible, string fileName, byte[] bytes, double latitude, double altitude, double longitude, DateTime satelliteTime, int satelliteCount, double direction, int vendor_Visit_XMissionStructures_PoleID, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Images_Insert", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_XMissionStructureID", ParameterDirection.Input, false, vendor_Visit_XMissionStructureFK));
                cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 200, ParameterDirection.Input, false, fileName));
                cmd.Parameters.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, solutionAvaible));
                cmd.Parameters.Add(DBHelper.CreateImageParameter("@Bytes", 0, ParameterDirection.Input, true, DBNull.Value));

                #region Vendor_Visit_XMissionStructures_PoleID
                if (vendor_Visit_XMissionStructures_PoleID == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_XMissionStructures_PoleID", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_XMissionStructures_PoleID", ParameterDirection.Input, false, vendor_Visit_XMissionStructures_PoleID));

                }
                #endregion

                #region Direction
                if (direction == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Direction", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Direction", ParameterDirection.Input, false, direction));
                }
                #endregion

                #region Latitude
                if (latitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, latitude));
                }
                #endregion

                #region Longitude
                if (longitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value));

                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, longitude));

                }
                #endregion

                #region Altitude
                if (altitude == double.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value));

                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, altitude));

                }
                #endregion

                #region SatelliteCount

                if (satelliteCount == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, satelliteCount));
                }



                #endregion

                #region SatelliteTime
                if (satelliteTime == DateTime.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, true, satelliteTime));
                }

                #endregion


                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Utility_Asset_XMissionStructures_ImageId", ParameterDirection.ReturnValue, false, 0));
                cmd.ExecuteNonQuery();
                tr.Commit();
                UpdateFileName((Int32)cmd.Parameters["@Utility_Asset_XMissionStructures_ImageId"].Value, vendor_Visit_XMissionStructureFK, fileName, connectionKey);
                return (Int32)cmd.Parameters["@Utility_Asset_XMissionStructures_ImageId"].Value;
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                return 0;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }

        }

        /// <summary>
        /// Update tb_Utility_Asset_XMissionStructures_Images Filename field by ImageId
        /// </summary>
        /// <param name="imageID"></param>
        /// <param name="vendor_Visit_XMissionStructureFK"></param>
        /// <param name="filename"></param>
        private static void UpdateFileName(int imageID, int utilityId, string filename, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                filename = utilityId.ToString() + "_" + imageID.ToString() + ".jpg";
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Images_Update_FileName", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@imageId", ParameterDirection.Input, false, imageID));
                cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 200, ParameterDirection.Input, false, filename));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Delete image
        /// </summary>
        /// <param name="imageId"></param>
        public static void Delete(Int32 imageId, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Images_Delete", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@imageId", ParameterDirection.Input, false, imageId));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        /// <summary>
        /// return byte[] with a Image
        /// </summary>
        /// <param name="imageID"></param>
        /// <returns></returns>
        public static byte[] GetServerImageBytes(int imageID, string connectionKey)
        {
            SqlCommand cmd = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructures_Images_SelectBytes", DBHelper.GetConnection(connectionKey));
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@utilityID", ParameterDirection.Input, false, imageID));
                return (byte[])cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        #endregion

    }
}
